package org.ytor.core.sqlflow.builder.support;

import org.ytor.core.sqlflow.SFunction;
import org.ytor.core.sqlflow.builder.AbsBuilder;
import org.ytor.core.sqlflow.builder.BuilderWrapper;
import org.ytor.core.sqlflow.builder.IOrderedParams;
import org.ytor.core.sqlflow.builder.select.ISelect;
import org.ytor.core.sqlflow.builder.select.SelectBuilder;

import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * created by yangtong on 2025/8/8 14:41:21
 * <br/>
 * 条件构造父类（可复用于 WHERE / HAVING / ON）
 */
public class ConditionBuilder extends AbsBuilder implements IOrderedParams {
    /**
     * where 子句条件数组
     */
    private final List<String> conditions = new ArrayList<>();
    /**
     * where 子句中的有序参数集合
     */
    private final List<Object> orderedParams = new ArrayList<>();
    /**
     * and or 连接符数组
     */
    private final List<String> operators = new ArrayList<>();
    /**
     * 当前逻辑连接符（默认为 AND）
     */
    private String currentOp = "AND";

    public ConditionBuilder(Map<Object, String> tableAliases) {
        super.tableAliases = Objects.requireNonNullElseGet(tableAliases, HashMap::new);
    }

    // ====================== 方法引用API ======================

    /**
     * =，等值匹配
     */
    public <T> ConditionBuilder eq(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " = " + wrap(value), currentOp);
        return this;
    }

    /**
     * =，等值匹配
     */
    public <T> ConditionBuilder eq(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            eq(field, value);
        }
        return this;
    }

    /**
     * 不等匹配
     */
    public <T> ConditionBuilder ne(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " != " + wrap(value), currentOp);
        return this;
    }

    /**
     * 不等匹配
     */
    public <T> ConditionBuilder ne(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            ne(field, value);
        }
        return this;
    }

    /**
     * 大于
     */
    public <T> ConditionBuilder gt(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " > " + wrap(value), currentOp);
        return this;
    }

    /**
     * 大于
     */
    public <T> ConditionBuilder gt(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            gt(field, value);
        }
        return this;
    }

    /**
     * 小于
     */
    public <T> ConditionBuilder lt(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " < " + wrap(value), currentOp);
        return this;
    }

    /**
     * 小于
     */
    public <T> ConditionBuilder lt(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            lt(field, value);
        }
        return this;
    }

    /**
     * 大于等于
     */
    public <T> ConditionBuilder ge(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " >= " + wrap(value), currentOp);
        return this;
    }

    /**
     * 大于等于
     */
    public <T> ConditionBuilder ge(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            ge(field, value);
        }
        return this;
    }

    /**
     * 小于等于
     */
    public <T> ConditionBuilder le(SFunction<T, ?> field, Object value) {
        addCondition(resolveField(field) + " <= " + wrap(value), currentOp);
        return this;
    }

    /**
     * 小于等于
     */
    public <T> ConditionBuilder le(Boolean condition, SFunction<T, ?> field, Object value) {
        if (condition) {
            le(field, value);
        }
        return this;
    }

    /**
     * 模糊匹配
     */
    public <T> ConditionBuilder like(SFunction<T, ?> field, String value) {
        addCondition(resolveField(field) + " LIKE " + wrap("%" + value + "%"), currentOp);
        return this;
    }

    /**
     * 模糊匹配
     */
    public <T> ConditionBuilder like(Boolean condition, SFunction<T, ?> field, String value) {
        if (condition) {
            like(field, value);
        }
        return this;
    }

    /**
     * 左模糊匹配
     */
    public <T> ConditionBuilder leftLike(SFunction<T, ?> field, String value) {
        addCondition(resolveField(field) + " LIKE " + wrap("%" + value), currentOp);
        return this;
    }

    /**
     * 左模糊匹配
     */
    public <T> ConditionBuilder leftLike(Boolean condition, SFunction<T, ?> field, String value) {
        if (condition) {
            leftLike(field, value);
        }
        return this;
    }

    /**
     * 模糊匹配
     */
    public <T> ConditionBuilder rightLike(SFunction<T, ?> field, String value) {
        addCondition(resolveField(field) + " LIKE " + wrap(value + "%"), currentOp);
        return this;
    }

    /**
     * 模糊匹配
     */
    public <T> ConditionBuilder rightLike(Boolean condition, SFunction<T, ?> field, String value) {
        if (condition) {
            rightLike(field, value);
        }
        return this;
    }

    /**
     * 不为空
     */
    public <T> ConditionBuilder isNull(SFunction<T, ?> field) {
        addCondition(resolveField(field) + " IS NULL", currentOp);
        return this;
    }

    /**
     * 不为空
     */
    public <T> ConditionBuilder isNull(Boolean condition, SFunction<T, ?> field) {
        if (condition) {
            isNull(field);
        }
        return this;
    }

    /**
     * 不为空
     */
    public <T> ConditionBuilder isNotNull(SFunction<T, ?> field) {
        addCondition(resolveField(field) + " IS NOT NULL", currentOp);
        return this;
    }

    /**
     * 不为空
     */
    public <T> ConditionBuilder isNotNull(Boolean condition, SFunction<T, ?> field) {
        if (condition) {
            isNotNull(field);
        }
        return this;
    }

    /**
     * in 匹配
     */
    public <T> ConditionBuilder in(SFunction<T, ?> field, Collection<?> values) {
        if (values == null || values.isEmpty()) return this;
        String inClause = values.stream().map(this::wrap).collect(Collectors.joining(", "));
        addCondition(resolveField(field) + " IN (" + inClause + ")", currentOp);
        return this;
    }

    /**
     * in 匹配
     */
    public <T> ConditionBuilder in(Boolean condition, SFunction<T, ?> field, Collection<?> values) {
        if (condition) {
            in(field, values);
        }
        return this;
    }

    /**
     * in 子查询
     */
    public <T> ConditionBuilder in(SFunction<T, ?> field, ISelect subSelect) {
        String left = resolveField(field);
        addCondition(left + " IN (" + subSelect.toSql() + ")", currentOp);
        // 合并参数顺序：父 -> (此处) 子查询
        this.orderedParams.addAll(subSelect.orderedParams());
        return this;
    }

    /**
     * in 子查询（条件版）
     */
    public <T> ConditionBuilder in(Boolean condition, SFunction<T, ?> field, SelectBuilder sub) {
        if (condition) {
            in(field, sub);
        }
        return this;
    }

    /**
     * between
     */
    public <T> ConditionBuilder between(SFunction<T, ?> field, Object start, Object end) {
        if (start == null || end == null) return this;
        addCondition(resolveField(field) + " BETWEEN " + wrap(start) + " AND " + wrap(end), currentOp);
        return this;
    }

    /**
     * between（条件版）
     */
    public <T> ConditionBuilder between(Boolean condition, SFunction<T, ?> field, Object start, Object end) {
        if (condition) {
            between(field, start, end);
        }
        return this;
    }

    /**
     * 列对列比较（=）
     */
    public <L, R> ConditionBuilder eq(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " = " + resolveField(right), currentOp);
        return this;
    }

    /**
     * 列对列比较（!=）
     */
    public <L, R> ConditionBuilder ne(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " != " + resolveField(right), currentOp);
        return this;
    }

    /**
     * 列对列比较（>）
     */
    public <L, R> ConditionBuilder gt(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " > " + resolveField(right), currentOp);
        return this;
    }

    /**
     * 列对列比较（>=）
     */
    public <L, R> ConditionBuilder ge(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " >= " + resolveField(right), currentOp);
        return this;
    }

    /**
     * 列对列比较（<）
     */
    public <L, R> ConditionBuilder lt(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " < " + resolveField(right), currentOp);
        return this;
    }

    /**
     * 列对列比较（<=）
     */
    public <L, R> ConditionBuilder le(SFunction<L, ?> left, SFunction<R, ?> right) {
        addCondition(resolveField(left) + " <= " + resolveField(right), currentOp);
        return this;
    }

    // ====================== 原生表达式API ======================

    /**
     * 原生表达式EQ
     * 例：eq("COUNT(1)", 10) -> COUNT(1) = ?
     */
    public ConditionBuilder eq(String expr, Object value) {
        addCondition(expr + " = " + wrap(value), currentOp);
        return this;
    }

    /**
     * 原生表达式EQ（带条件版）
     */
    public ConditionBuilder eq(Boolean condition, String expr, Object value) {
        if (condition) {
            eq(expr, value);
        }
        return this;
    }

    /**
     * 原生表达式比较：不等
     */
    public ConditionBuilder ne(String expr, Object value) {
        addCondition(expr + " != " + wrap(value), currentOp);
        return this;
    }

    /** 原生表达式比较：> */
    public ConditionBuilder gt(String expr, Object value) {
        addCondition(expr + " > " + wrap(value), currentOp);
        return this;
    }

    /** 原生表达式比较：>= */
    public ConditionBuilder ge(String expr, Object value) {
        addCondition(expr + " >= " + wrap(value), currentOp);
        return this;
    }

    /** 原生表达式比较：< */
    public ConditionBuilder lt(String expr, Object value) {
        addCondition(expr + " < " + wrap(value), currentOp);
        return this;
    }

    /** 原生表达式比较：<= */
    public ConditionBuilder le(String expr, Object value) {
        addCondition(expr + " <= " + wrap(value), currentOp);
        return this;
    }

    public ConditionBuilder like(String expr, Object value) {
        addCondition(expr + " LIKE " + wrap("%" + value + "%"), currentOp);
        return this;
    }

    /**
     * 原生表达式 between
     * 例：between("SUM(amount)", 100, 200) -> SUM(amount) BETWEEN ? AND ?
     */
    public ConditionBuilder between(String expr, Object start, Object end) {
        if (start == null || end == null) return this;
        addCondition(expr + " BETWEEN " + wrap(start) + " AND " + wrap(end), currentOp);
        return this;
    }

    /**
     * 原生表达式 IN
     * 例：in("YEAR(created_at)", List.of(2023, 2024)) -> YEAR(created_at) IN (?, ?)
     */
    public ConditionBuilder in(String expr, Collection<?> values) {
        if (values == null || values.isEmpty()) return this;
        String inClause = values.stream().map(this::wrap).collect(Collectors.joining(", "));
        addCondition(expr + " IN (" + inClause + ")", currentOp);
        return this;
    }

    /**
     * 原生表达式 IN（条件版）
     * 例：in("YEAR(created_at)", List.of(2023, 2024)) -> YEAR(created_at) IN (?, ?)
     */
    public ConditionBuilder in(Boolean condition, String expr, Collection<?> values) {
        if (condition) {
            in(expr, values);
        }
        return this;
    }

    /**
     * 自定义 SQL 片段
     * 例：expr("SUM(amount) > ? AND COUNT(1) <= ?", 100, 10)
     */
    public ConditionBuilder expr(String sqlFragmentWithPlaceholders, Object... args) {
        if (sqlFragmentWithPlaceholders == null || sqlFragmentWithPlaceholders.isEmpty()) return this;
        addCondition("(" + sqlFragmentWithPlaceholders + ")", currentOp);
        if (args != null && args.length > 0) {
            Collections.addAll(orderedParams, args);
        }
        return this;
    }

    // ====================== 逻辑运算 & 嵌套 ======================

    /**
     * 切换成 or
     */
    public ConditionBuilder or() {
        this.currentOp = "OR";
        return this;
    }

    /**
     * 切换成 and
     */
    public ConditionBuilder and() {
        this.currentOp = "AND";
        return this;
    }

    /**
     * or 嵌套
     */
    public ConditionBuilder or(Function<ConditionBuilder, ConditionBuilder> nestedBuilder) {
        return group(nestedBuilder, "OR");
    }

    /**
     * and 嵌套
     */
    public ConditionBuilder and(Function<ConditionBuilder, ConditionBuilder> nestedBuilder) {
        return group(nestedBuilder, "AND");
    }

    /**
     * 将外部的where条件合并进当前的where构造器
     */
    public ConditionBuilder merger(ConditionBuilder subWhere) {
        if (subWhere == null) {
            return this;
        }
        String sql = subWhere.toSql();
        if (sql != null && !sql.isEmpty()) {
            addCondition("(" + sql + ")", "AND");
            this.orderedParams.addAll(subWhere.orderedParams());
        }
        return this;
    }

    /**
     * 拼接SQL where子句
     */
    @Override
    public String toSql() {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < conditions.size(); i++) {
            String op = operators.get(i);
            if (!op.isEmpty()) {
                sb.append(" ").append(op).append(" ");
            }
            sb.append(conditions.get(i));
        }
        return sb.toString();
    }

    @Override
    public List<Object> orderedParams() {
        return orderedParams;
    }

    /**
     * 括号分组
     */
    private ConditionBuilder group(Function<ConditionBuilder, ConditionBuilder> builderFn, String logic) {
        ConditionBuilder inner = new ConditionBuilder(tableAliases);
        builderFn.apply(inner);
        String nestedExpr = inner.toSql();
        if (!nestedExpr.isEmpty()) {
            this.orderedParams.addAll(inner.orderedParams);
            addCondition("(" + nestedExpr + ")", logic);
        }
        return this;
    }

    private void addCondition(String condition, String operator) {
        if (!conditions.isEmpty()) {
            operators.add(operator);
        } else {
            operators.add(""); // 第一个条件不加逻辑符
        }
        conditions.add(condition);
        this.currentOp = "AND"; // 重置为默认
    }

    private String wrap(Object val) {
        if (val == null) {
            return "NULL";
        }

        // 如果该值被 BuilderWrapper 包装，则不需要添加占位符，直接返回
        if (val instanceof BuilderWrapper bwVal) {
            return bwVal.exprValue();
        }

        orderedParams.add(val);
        return "?";
    }
}
